package 临时工具.shangyu2;

import java.io.Serializable;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class 处理图片和视频链接 implements Serializable {
    // 数据库连接信息
    private static final String DB_URL = "jdbc:mysql://211.90.241.209:3306/test?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "Sxzc@2022..";
    private static final String LOCAL_PATH = "E:\\temp\\gwjc";
    private static final List<String[]> replStartsWith = new ArrayList<String[]>(){{
        //add(new String[]{"http://39.184.194.199:9200/file/gwjc/百官所/23年", "http://39.184.194.199:9200/file/gwjc/百官所/23百官"});
        //add(new String[]{"http://39.184.194.199:9200/file/gwjc/百官所/24年", "http://39.184.194.199:9200/file/gwjc/百官所/24百官"});
    }};
    private static final List<String[]> replAll = new ArrayList<String[]>(){{

        //add(new String[]{"用户管", "入户管"});
    }};

    public static void main(String[] args) {
        try {
            // 加载驱动（可省略）
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 建立连接
            Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            System.out.println("连接成功");

            String selectSql = "select * from env_hidden_dangers_point where yhsp_new is null and yhsp_old like 'http://39.184.194.199:9200/file/gwjc/%' ";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(selectSql);


            while (rs.next()) {
                System.out.print("第" + rs.getRow()+ "条数据 ");
                Long id = rs.getLong("id");
                String oldUrl = rs.getString("yhsp_old");
                if (isValidURL(oldUrl)){
                    updateData(id, oldUrl, conn);
                }else {
                    String newUrl = getNewUrl(oldUrl);
                    if (isValidURL(newUrl)){
                        updateData(id, newUrl, conn);
                    }else{
                    }
                }
            }

            // 关闭资源
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String getNewUrl(String oldUrl) {
        for (String[] s : replStartsWith) {
            if (oldUrl.startsWith(s[0])){
                oldUrl= oldUrl.replace(s[0], s[1]);
            }
        }
        for (String[] s : replAll) {
            oldUrl= oldUrl.replace(s[0], s[1]);
        }
        return oldUrl;
    }

    public static boolean isValidURL(String urlString) {
        try {
            String replace = urlString.replace("http://39.184.194.199:9200/file/gwjc", LOCAL_PATH);
            if (Files.exists(Paths.get(replace))) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            return false;
        }
    }
    public static void updateData(Long id, String newUrl, Connection conn) {

        String updateSql = "UPDATE env_hidden_dangers_point SET yhsp_new = ? WHERE id = ?";
        try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
            updateStmt.setString(1,newUrl );
            updateStmt.setLong(2, id);
            int updated = updateStmt.executeUpdate();
            System.out.println("已更新url为：" + newUrl);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

